﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace BHair.Base
{
    public partial class frmyczh : Form
    {
        public frmyczh()
        {
            InitializeComponent();
        }
        string cardid;
        string constr = ConfigurationManager.ConnectionStrings["BHair.Properties.Settings.BHairDBConnectionString"].ConnectionString;
        private void button5_Click(object sender, EventArgs e)
        {
            if (txtcardID.Text != string.Empty)
            {
                SqlConnection connc = new SqlConnection(constr);
                connc.Open();
                string cardsql = string.Format("SELECT mid,mname, mphone FROM member where mid='{0}' or mphone='{1}'", txtcardID.Text, txtcardID.Text);
                SqlCommand cardcomm = new SqlCommand(cardsql, connc);
                SqlDataReader sdrcard = cardcomm.ExecuteReader();
                while (sdrcard.Read())
                {
                    cardid = sdrcard["mid"].ToString();
                    lbname.Text = sdrcard["mname"].ToString();
                    lbphone.Text = sdrcard["mphone"].ToString();
                }
                //根据卡号查询预存项目
                SqlConnection conn = new SqlConnection(constr);
                conn.Open();
                string sql = string.Format("SELECT [jcspid] as 项目编号 ,items.iname as 预存项目,[jcnum] as 预存次数 FROM [BHairDB].[dbo].[ycxm] join items on ycxm.jcspid=items.iid where ycxm.cardID='{0}'", cardid);
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                dataGridView1.DataSource = dt;
                connc.Close();
                conn.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
            int index = this.dataGridView2.Rows.Add();
           
            this.dataGridView2.Rows[index].Cells[0].Value = dataGridView1.SelectedCells[0].Value.ToString();
            this.dataGridView2.Rows[index].Cells[1].Value = dataGridView1.SelectedCells[1].Value.ToString();
            this.dataGridView2.Rows[index].Cells[2].Value = dataGridView1.SelectedCells[2].Value.ToString();
            var q = dataGridView1.CurrentRow;

            dataGridView1.Rows.Remove(q);
        }

        private void txtcardID_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                if (txtcardID.Text != string.Empty)
                {
                    SqlConnection connc = new SqlConnection(constr);
                    connc.Open();
                    string cardsql = string.Format("SELECT mid,mname, mphone FROM member where mid='{0}' or mphone='{1}'", txtcardID.Text, txtcardID.Text);
                    SqlCommand cardcomm = new SqlCommand(cardsql, connc);
                    SqlDataReader sdrcard = cardcomm.ExecuteReader();
                    while (sdrcard.Read())
                    {
                        cardid = sdrcard["mid"].ToString();
                        lbname.Text = sdrcard["mname"].ToString();
                        lbphone.Text = sdrcard["mphone"].ToString();
                    }
                    //根据卡号查询预存项目
                    SqlConnection conn = new SqlConnection(constr);
                    conn.Open();
                    string sql = string.Format("SELECT [jcspid] as 项目编号 ,items.iname as 预存项目,[jcnum] as 预存次数 FROM [BHairDB].[dbo].[ycxm] join items on ycxm.jcspid=items.iid where ycxm.cardID='{0}'", cardid);
                    SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    dataGridView1.DataSource = dt;
                    connc.Close();
                    conn.Close();
                }
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //将项目次数增加
            //判断数据是否为空
            if (dataGridView2.Rows.Count != 0)
            {
                int rows = 0;
                decimal cs = 0;
                for (int i = 0; i < dataGridView2.Rows.Count; i++)
                {
                    SqlConnection conn = new SqlConnection(constr);
                    SqlConnection conn1 = new SqlConnection(constr);
                    SqlConnection conn2 = new SqlConnection(constr);
                    conn.Open();
                    string xmID = this.dataGridView2.Rows[i].Cells[0].Value.ToString();
                    decimal czcs = decimal.Parse(this.dataGridView2.Rows[i].Cells[2].Value.ToString());
                    string sql = string.Format("SELECT [cardID],[jcspid],[jcnum] FROM [BHairDB].[dbo].[csmx] join items on csmx.jcspid=items.iid where cardID='{0}' and jcspid='{1}'", cardid, xmID);
                    string sql1 = string.Format("select COUNT(*) from csmx join items on csmx.jcspid= items.iid where cardID= '{0}' and jcspid ='{1}'", cardid, dataGridView2.Rows[i].Cells[0].Value);
                    SqlCommand comm1 = new SqlCommand(sql1, conn1);
                    conn1.Open();
                    rows = Int32.Parse(comm1.ExecuteScalar().ToString());
                    if (rows == 0)
                    {
                        string sql2 = string.Format("INSERT INTO [BHairDB].[dbo].[csmx]([cardID],[jcspid] ,[jcnum],jcxmname,备注)VALUES('{0}','{1}',{2},'{3}','')", cardid, dataGridView2.Rows[i].Cells[0].Value, dataGridView2.Rows[i].Cells[2].Value, dataGridView2.Rows[i].Cells[1].Value);
                        conn2.Open();
                        SqlCommand comm2 = new SqlCommand(sql2, conn2);
                        comm2.ExecuteNonQuery();
                    }
                    else
                    {

                        SqlCommand comm = new SqlCommand(sql, conn);
                        SqlDataReader sdr = comm.ExecuteReader();
                        while (sdr.Read())
                        {
                            cs = decimal.Parse(sdr["jcnum"].ToString());

                            decimal upcs = czcs + cs;
                            string sql3 = string.Format("UPDATE [BHairDB].[dbo].[csmx] SET [jcnum] = {0} WHERE jcspid='{1}' and cardID='{2}'", upcs, xmID, cardid);
                            SqlConnection conn3 = new SqlConnection(constr);
                            conn3.Open();
                            SqlCommand comm3 = new SqlCommand(sql3, conn3);
                            comm3.ExecuteNonQuery();
                            conn3.Close();
                        }
                    }
                    SqlConnection conn99 = new SqlConnection(constr);
                    conn99.Open();
                    string sql99 = string.Format("DELETE FROM ycxm where cardID='{0}' and jcspid='{1}'",cardid,xmID);
                    SqlCommand comm99 = new SqlCommand(sql99,conn99);
                    comm99.ExecuteNonQuery();



                    conn.Close();
                    conn1.Close();
                    conn2.Close();
                    conn99.Close();

                }
                MessageBox.Show("修改成功！");
                shuaxin();
                //djhshuaxin();
                //djh();

            }
            else
            {
                MessageBox.Show("请正确选择预存项目！");
            }


        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        public void shuaxin()
        {
            txtcardID.Text = "";
            lbname.Text = "";
            lbphone.Text = "";
            dataGridView1.DataSource = null;
            dataGridView2.Rows.Clear();
        }
    }
}
